#include "mainwindow.h"
#include "ui_mainwindow.h"

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    this->startSystem();
    this->displayDB();
}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::startSystem(){
    createDB();
    createDBtable();
}

bool MainWindow::createDB(){
    //    if(QSqlDatabase::contains("qt_sql_default_connection"))
    //      db = QSqlDatabase::database("qt_sql_default_connection");
    if(QSqlDatabase::contains("qt_sql_default_connection")){
        db=QSqlDatabase::database("qt_sql_default_connection");
    }
    else{
        db=QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName("student_Information");
        db.setHostName("localhost");
        db.setUserName("helloworld");
        db.setPassword("123456");
        bool createDatabaseOK=db.open("helloworld","123456");
        if(createDatabaseOK){
            ui->Stuate_label->setText("数据库连接成功!");
        }
        else{
            ui->Stuate_label->setText("create Database unsuccessfully!");
        }
        return createDatabaseOK;
    }
}

bool MainWindow::createDBtable(){
    QSqlQuery query;
    QString commandStr="create table information(id int primary key,sName nvarchar(40),sSex nvarchar(4),sScore float)";
    bool createDBtableOK=query.exec(commandStr);
    if(createDBtableOK){
        ui->Stuate_label->setText(ui->Stuate_label->text().append("\n数据库表创建成功"));
    }
    else{
        ui->Stuate_label->setText(ui->Stuate_label->text().append("\n数据库表创建失败(该表已存在)"));
    }
    return createDBtableOK;
}

int MainWindow::getDBRecordCount(){
    QString commandStr="select * from information";
    QSqlQuery query;
    query.exec(commandStr);
    query.last();
    qDebug()<<"query.at()="<<query.at();
    if(query.at()!=-2){
        LastnumIndex=query.value("id").toInt();
    }
    else{
        LastnumIndex=0;
    }
    return query.at()+1;
}

//显示功能
void MainWindow::displayDB(){
    QSqlQueryModel *model = new QSqlQueryModel(ui->tableView);
    QString commandStr="select * from information";
    QSqlQuery query;
    if(this->getDBRecordCount()==-1){
        ui->Stuate_label->setText("无记录数据");
        ui->DisplayDB->setText("无数据");
    }
    else{
        ui->Stuate_label->setText(ui->Stuate_label->text().append("\n现有记录的条数为:"+QString::number(getDBRecordCount())));
        QString Everybody;
        query.exec(commandStr);
        while(query.next()){
            QString recStr=query.value(0).toString()+" "+query.value(1).toString()+" "+query.value(2).toString()+" "+query.value(3).toString();
            //        ui->Stuate_label->setText(ui->Stuate_label->text().append("\n")+recStr);
            Everybody=Everybody+recStr+"\n";
        }
        ui->DisplayDB->setText(Everybody);
    }
    model->setQuery(query);
    model->setHeaderData(0, Qt::Horizontal, "序号");
    model->setHeaderData(1, Qt::Horizontal, "姓名");
    model->setHeaderData(2, Qt::Horizontal, "性别");
    model->setHeaderData(3, Qt::Horizontal, "成绩");
    ui->tableView->setModel(model);
    ui->tableView->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->tableView->setSelectionMode(QAbstractItemView::SelectionMode::SingleSelection);
}

//插入数据
bool MainWindow::insertDB(int id,QString name,QString sex,float score){
    QSqlQuery query;
    query.prepare("insert into information values(?,?,?,?)");
    query.bindValue(0,id);
    query.bindValue(1,name);
    query.bindValue(2,sex);
    query.bindValue(3,score);
    return query.exec();
}

//插入按钮监控
void MainWindow::on_Insert_pushButton_clicked()
{
    int idnum=0;
    count_number=getDBRecordCount();
    if(count_number==-1){
        idnum=1;
    }
    else{
        idnum=LastnumIndex+1;
    }
    QString nameStr=ui->stuNamelineEdit->text().trimmed();
    QString sexStr=ui->stuSexcomboBox->currentText().trimmed();
    float scoreNum=(ui->stuScorelineEdit->text().trimmed()).toFloat();
    insertDB(idnum,nameStr,sexStr,scoreNum);
    this->LastnumIndex=idnum;
    ui->Stuate_label->clear();
    displayDB();
}

//显示按钮监控
void MainWindow::on_showData_Button_clicked(){
    ui->Stuate_label->clear();
    displayDB();
}

//删除全部数据按钮监控
void MainWindow::on_DeleteAll_pushButton_clicked(){
    QString commandStr="delete from information where 1=1";
    QSqlQuery query;
    if(query.exec(commandStr)){
        ui->Stuate_label->setText("所有数据已经删除");
        ui->DisplayDB->setText("无数据");
    }
    else{
        ui->Stuate_label->setText("删除失败！");
    }
    displayDB();
}

//删除单条数据按钮监控
void MainWindow::on_DeleteThis_pushButton_clicked()
{
    if(seachHasID(ui->IDSearch_lineEdit->text().toInt())){
        QString commandStr="delete from information where id="+ui->IDSearch_lineEdit->text().trimmed()+"";
        QSqlQuery query;
        if(query.exec(commandStr)){
            ui->Stuate_label->setText("数据库里编号为"+ui->IDSearch_lineEdit->text().trimmed()+"的记录删除成功");
        } else {
            ui->Stuate_label->setText("数据库里编号为"+ui->IDSearch_lineEdit->text().trimmed()+"的记录删除失败");
        }
    } else {
        ui->Stuate_label->setText("数据库里编号为"+ui->IDSearch_lineEdit->text().trimmed()+"的记录不存在");
    }
    this->displayDB();
}

//搜索按钮监控
void MainWindow::on_Search_pushButton_clicked()
{
    if(seachHasID(ui->IDSearch_lineEdit->text().toInt())){
        QString commandStr="select * from information where id="+ui->IDSearch_lineEdit->text().trimmed()+"";
        QSqlQuery query;
        if(query.exec(commandStr)){
            ui->Stuate_label->setText("数据库里编号为"+ui->IDSearch_lineEdit->text().trimmed()+"的记录查找成功");
            while(query.next()){
                QString recStr=query.value(0).toString()+" "+query.value(1).toString()+" "+query.value(2).toString()+" "+query.value(3).toString();
                ui->DisplayDB->setText(recStr);
            }
        } else {
            ui->Stuate_label->setText("数据库里编号为"+ui->IDSearch_lineEdit->text().trimmed()+"的记录查找失败");
            ui->DisplayDB->clear();
        }
    } else {
        ui->Stuate_label->setText("数据库里编号为"+ui->IDSearch_lineEdit->text().trimmed()+"的记录不存在");
        ui->DisplayDB->clear();
    }
}

//根据idnum判断是否搜索成功
bool MainWindow::seachHasID(int idnum){
    if(getDBRecordCount()==-1){
        return false;
    }
    else{
        QString commandStr="select * from information";
        QSqlQuery query;
        query.exec(commandStr);
        while(query.next()){
            if(query.value("id").toInt()==idnum){
                return  true;
            }
        }
    }
    return false;
}
